import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler, MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, \
    ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime


#数据读取
bf_no = 1
#铁水硫
xlsx_name = 'D:/repos/sicost/fe_s_' + str(bf_no) + '.xlsx'
df1 = pd.read_excel(xlsx_name)
df1.columns = df1.columns.str.upper()
df1['PROD_DATE'] = df1['PROD_DATE'].astype(str)
df1.drop(['UNIT_NO'], axis=1, inplace=True)
#燃料比
xlsx_name = 'D:/repos/sicost/ranliaobi_' + str(bf_no) + '.xlsx'
df2 = pd.read_excel(xlsx_name)
df2.columns = df2.columns.str.upper()
df2['PROD_DATE'] = df2['PROD_DATE'].astype(str)
df2.drop(['UNIT_NO'], axis=1, inplace=True)
df2.drop(['PROC_UNIT'], axis=1, inplace=True)
#喷吹煤硫
xlsx_name = 'D:/repos/sicost/penchuimei_' + str(bf_no) + '.xlsx'
df3 = pd.read_excel(xlsx_name)
df3.columns = df3.columns.str.upper()
df3['MAT_SAMPLE_DATE'] = df3['MAT_SAMPLE_DATE'].astype(str)
df3.rename(columns={'MAT_SAMPLE_DATE': 'PROD_DATE'}, inplace=True)
#焦炭硫
xlsx_name = 'D:/repos/sicost/jiaotan.xlsx'
df4 = pd.read_excel(xlsx_name)
df4.columns = df4.columns.str.upper()
df4['MAT_SAMPLE_DATE'] = df4['MAT_SAMPLE_DATE'].astype(str)
df4.rename(columns={'MAT_SAMPLE_DATE': 'PROD_DATE'}, inplace=True)
#数据关联
v = ['PROD_DATE']
df_12 = pd.merge(df1, df2, on=v, how='left')
df_123 = pd.merge(df_12, df3, on=v, how='left')
df_1234 = pd.merge(df_123, df4, on=v, how='left')
#数据清洗
#去除0和nan
def clean_data(df, gamma):
    column_name_list = df.columns.tolist()
    column_name_list.remove('PROD_DATE')
    column_name_num = len(column_name_list)
    clean_str_start = 'df_new = df['
    clean_str_end = ']'
    ldict1 = {}
    for i in range(0, column_name_num):
        print(i)
        print(column_name_list[i])
        column_name_tmp = column_name_list[i]
        exec("q1_{} = df['{}'].quantile(0.25)".format(i, column_name_tmp), locals(), ldict1)
        exec("q3_{} = df['{}'].quantile(0.75)".format(i, column_name_tmp), locals(), ldict1)
        exec("iqr_val_{} = q3_{} - q1_{}".format(i, i, i), locals(), ldict1)
        exec(
            '''clean_str1 = "(df['{}'] >= ldict1['q1_{}'] - gamma * ldict1['iqr_val_{}'])"'''.format(column_name_tmp, i,
                                                                                                     i), locals(),
            ldict1)
        exec('''clean_str2 = "(df['{}'] < ldict1['q3_{}'] + gamma * ldict1['iqr_val_{}'])"'''.format(column_name_tmp, i,
                                                                                                     i), locals(),
             ldict1)
        exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
        clean_str1 = ldict1["clean_str1"]
        clean_str2 = ldict1["clean_str2"]
        clean_str3 = ldict1["clean_str3"]
        if i == 0:
            clean_str_start = clean_str_start + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
        else:
            clean_str_start = clean_str_start + ' & ' + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
    clean_str = clean_str_start + clean_str_end
    print(clean_str)
    exec(clean_str, locals(), ldict1)
    df_new = ldict1["df_new"]
    df_new = df_new.reset_index(drop=True)
    return df_new
df_clean_nan = df_1234.dropna()
df_clean_0 = df_clean_nan[df_clean_nan.ne(0).all(axis=1)]

gamma = 3
# df0_clean = clean_data(df_1234, gamma)
# df0_clean2 = clean_data(df_cleaned, gamma)
df_clean_liqun = clean_data(df_clean_0, gamma)
df_clean_liqun.drop(['SUM_CACULATE_IRON_WGT'], axis=1, inplace=True)
df0 = df_clean_liqun.copy()
df0['ZONGJIAOBI'] = df0['JIAOBI'] + df0['XIAOJIAOBI']
df0['JIAOTAN_DAIRULIU'] = df0['JIAOTAN_S'] * df0['ZONGJIAOBI']
df0['PENCHUIMEI_DAIRULIU'] = df0['PENCHUIMEI_S'] * df0['MEIBI']
df0['KUANGSHI_DAIRULIU'] = df0['COMPUTE_FILL_S_VALUE'] - df0['JIAOTAN_DAIRULIU'] - df0['PENCHUIMEI_DAIRULIU']
q1 = df0['KUANGSHI_DAIRULIU'].quantile(0.25)
q3 = df0['KUANGSHI_DAIRULIU'].quantile(0.75)
iqr_val = q3 - q1
df0_new = df0[(df0['KUANGSHI_DAIRULIU'] < q3 + gamma * iqr_val) & (df0['KUANGSHI_DAIRULIU'] >= q1 - gamma * iqr_val) & (df0['KUANGSHI_DAIRULIU'] > 0)]
df0_new = df0_new.reset_index(drop=True)

#煤种使用数量及价格
xlsx_name = 'D:/repos/sicost/COKE2使用数量.xlsx'
df5 = pd.read_excel(xlsx_name)
df5.columns = df5.columns.str.upper()
df5.WT.fillna(0, inplace=True)
df5.AD_WT.fillna(0, inplace=True)
df5_new = df5[(df5['WT'] > 0) | (df5['AD_WT'] > 0)]
df5_new = df5_new.reset_index(drop=True)

#煤质
xlsx_name = 'D:/repos/sicost/COKE2煤质.xlsx'
df6 = pd.read_excel(xlsx_name)
df6.columns = df6.columns.str.upper()
df6.drop(['VAR'], axis=1, inplace=True)
df6.drop(['CLASS'], axis=1, inplace=True)
df6.drop(['SOURCE'], axis=1, inplace=True)
df6.drop(['PROD_DSCR'], axis=1, inplace=True)
df6.drop(['H2O'], axis=1, inplace=True)
df6.drop(['ASH'], axis=1, inplace=True)
df6.drop(['COKING_COALBLD_BOND_IND'], axis=1, inplace=True)
df6.drop(['M_L3_JZCHD_Y'], axis=1, inplace=True)
df6.drop(['COKING_COALBLD_GIESFLU'], axis=1, inplace=True)
df6.drop(['L_M_AB'], axis=1, inplace=True)
df6.drop(['C'], axis=1, inplace=True)
df6.drop(['COKE_HOTVALUE'], axis=1, inplace=True)
v = ['PROD_CODE']
df_56 = pd.merge(df5_new, df6, on=v, how='left')
df_56['TOTAL_COKE_VM'] = df_56['COKE_VM'] * df_56['WT']
df_56['TOTAL_S'] = df_56['S'] * df_56['WT']
df_56['TOTAL_PRICE'] = df_56['PRICE'] * df_56['WT']
df_56['AD_TOTAL_COKE_VM'] = df_56['COKE_VM'] * df_56['AD_WT']
df_56['AD_TOTAL_S'] = df_56['S'] * df_56['AD_WT']
df_56['AD_TOTAL_PRICE'] = df_56['PRICE'] * df_56['AD_WT']

total_wt = df_56['WT'].sum()
ad_total_wt = df_56['AD_WT'].sum()
total_s = df_56['TOTAL_S'].sum()
ad_total_s = df_56['AD_TOTAL_S'].sum()
total_vm = df_56['TOTAL_COKE_VM'].sum()
ad_total_vm = df_56['AD_TOTAL_COKE_VM'].sum()
total_price = df_56['TOTAL_PRICE'].sum()
ad_total_price = df_56['AD_TOTAL_PRICE'].sum()
old_unit_price = total_price / total_wt
new_unit_price = ad_total_price / ad_total_wt
old_s = total_s / total_wt
new_s = ad_total_s / ad_total_wt
old_vm = total_vm / total_wt
new_vm = ad_total_vm / ad_total_wt
data_jiaotancanshu = pd.read_excel('COKE2参数.xlsx')
data_jiaotancanshu.columns = data_jiaotancanshu.columns.str.upper()
parm_4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_4')]
parm_4 = parm_4['PARM_CALC'].values[0]
parm_5 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_5')]
parm_5 = parm_5['PARM_CALC'].values[0]
parm_6 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_6')]
parm_6 = parm_6['PARM_CALC'].values[0]
old_jiaotan_s = parm_4 + parm_5 * (old_s) / (100 - old_vm) - parm_6 * (old_vm)
new_jiaotan_s = parm_4 + parm_5 * (new_s) / (100 - new_vm) - parm_6 * (new_vm)

# jiaotan_s = 0.69666
penchuimei_s = 0.37750
meibi = 172.2
zongjiaobi = 324.565
delta_unit_price = new_unit_price - old_unit_price
delta_jiaotan_s = new_jiaotan_s - old_jiaotan_s
delta_dairuliu = delta_jiaotan_s * zongjiaobi
bf_no = 1
avg_iron_temp = 1511
avg_c_s_value = 122
compute_slag_rate = 303
compute_fill_s_value = 349
old_dairuliu = compute_fill_s_value
new_dairuliu = old_dairuliu + delta_dairuliu
from Predict_FESJob import Predict_FESJob
y_pred_output = Predict_FESJob(p_bf_no=bf_no, p_avg_iron_temp=avg_iron_temp, p_avg_c_s_value=avg_c_s_value,
                               p_compute_slag_rate=compute_slag_rate,
                               p_compute_fill_s_value=new_dairuliu).execute()

print('finish')